/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package lib.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import options.DataBase;

/**
 *
 * @author amrmo_sho
 */
public class DBClass implements DataBase {

    String ErrorMessage = "--";
    Connection con;
    Statement st;
    ResultSet res;
    ResultSetMetaData resMetaData;
    String sql;
    String sqlString;

    public String setOpenCon() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(connectionString, dbUser, dbPassword);
        } catch (SQLException ex) {
            ErrorMessage += ex.getMessage();
            Logger.getLogger(DBClass.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            ErrorMessage += ex.getMessage();
        }
        return ErrorMessage;
    }

    public void setCloseCon() {
        try {
            if (res != null) {
                res.close();
                res = null;
            }
            if (st != null) {
                st.close();
                st = null;
            }
            if (con != null) {
                con.close();
                con = null;
            }
        } catch (SQLException ex) {
            //utils.outPrint(response, ex.getMessage());
            ErrorMessage += ex.getMessage();
        }


    }

    private ResultSet getDataFull(String table, String Cdata, String Where) {
        //  this.openCon();

        ResultSet re = null;
        try {
            if (Cdata.trim().equals("")) {
                Cdata = "*";
            }
            Statement st2 = con.createStatement();
            if (!Where.equals("")) {
                sql = "SELECT " + Cdata + " FROM " + table + " WHERE " + Where;
            } else {

                sql = "SELECT " + Cdata + " FROM " + table;
            }


            sqlString += "--" + sql;
            re = st2.executeQuery(sql);

        } catch (SQLException ex) {
            ErrorMessage += ex.getMessage();
        }
        return re;
    }

    public ResultSet getDataWithOutCon(String table) {
        ResultSet rs = this.getDataFull(table, "*", "");
        return rs;
    }

    public ResultSet getDataWithOutCon(String table, String Cdata) {
        ResultSet rs = this.getDataFull(table, Cdata, "");
        return rs;
    }

    public ResultSet getDataWithOutCon(String table, String Cdata, String Where) {
        ResultSet rs = this.getDataFull(table, Cdata, Where);
        return rs;
    }

    public ResultSet getData(String table) {

        this.setOpenCon();
        ResultSet rs = this.getDataFull(table, "*", "");
        Map<String, String> map = new HashMap<String, String>();

        /*while(rs.next()){
        
        
         }  */
        this.setCloseCon();
        return rs;
    }

    public ResultSet getData(String table, String Cdata) {

        this.setOpenCon();
        ResultSet rs = this.getDataFull(table, Cdata, "");
        this.setCloseCon();
        return rs;
    }

    public ResultSet getData(String table, String Cdata, String Where) {

        this.setOpenCon();
        ResultSet rs = this.getDataFull(table, Cdata, Where);
        this.setCloseCon();
        return rs;
    }
////////////////////////////////////////////////////////////////////////// ROW

    private int RowCount(String table, String Where) {
        int rowCount = 0;
        try {
            res = getDataFull(table, "", Where);
            if (res != null) {

                res.last();
                rowCount = res.getRow();
            }

        } catch (SQLException ex) {

            ErrorMessage += ex.getMessage();
        }
        return rowCount;
    }

    public int getRowCount(String table, String Where) {

        int returnData;
        this.setOpenCon();
        returnData = this.RowCount(table, Where);
        this.setOpenCon();
        return returnData;


    }

    public int getRowCount(String table) {
        int returnData;
        this.setOpenCon();
        returnData = this.RowCount(table, "");
        this.setOpenCon();
        return returnData;


    }

    /////////////////////////////////////////////////////////////////////////
    private String[] getFullColumn(String table, String Cdata, String Where) {

        int clu_num = getRowCount(table, Where);
        String[] returnArray = null;
        int i = 0;

        try {
            res = getDataFull(table, Cdata, Where);
            if (res != null) {
                returnArray = new String[clu_num];
                while (res.next()) {
                    returnArray[i] = res.getString(Cdata);
                    i++;
                }
            }

        } catch (SQLException ex) {

            ErrorMessage += ex.getMessage();

        }
        return returnArray;
    }

    public String[] getColumn(String table, String Cdata) {
        this.setOpenCon();
        String[] returnData = this.getFullColumn(table, Cdata, "");
        this.setCloseCon();
        return returnData;
    }

    public String[] getColumn(String table, String Cdata, String Where) {
        this.setOpenCon();
        String[] returnData = this.getFullColumn(table, Cdata, Where);

        setCloseCon();
        return returnData;
    }

    ////////////////////////////////getFullColumn
    ////////////////////////////////getDataToStringarray
    private String[] FullRow(String table, String Cdata, String Where) {

        String[] returnArray = null;

        try {




            ResultSet re = this.getDataFull(table, Cdata, Where);

            resMetaData = re.getMetaData();
            int clu_num = resMetaData.getColumnCount();
            returnArray = new String[clu_num];

            int mu = 0;

            if (re.next()) {
                for (int c = 0; c < clu_num; c++) {


                    returnArray[c] = re.getString(c + 1);


                }
            }






        } catch (SQLException ex) {
        }

        return returnArray;
    }

    public String[] getRow(String table) {
        this.setOpenCon();
        String[] returnData = this.FullRow(table, "", "");

        this.setCloseCon();
        return returnData;
    }

    public String[] getRow(String table, String cdata, String where) {
        this.setOpenCon();
        String[] returnData = this.FullRow(table, cdata, where);

        this.setCloseCon();
        return returnData;
    }

    public String[] getRow(String table, String Where) {
        this.setOpenCon();
        String[] returnData = this.FullRow(table, "", Where);
        this.setCloseCon();
        return returnData;
    }

    public String[] getRow(String table, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        String[] returnData = this.FullRow(table, "", "id=" + pid);
        this.setCloseCon();
        return returnData;
    }

    ////////////////////////////////getValue
    private String getFullValue(String table, String Cdata, String Where) {

        String return_string = "";
        try {


            res = this.getDataFull(table, Cdata, Where);
            if (res.next()) {


                return_string = res.getString(Cdata);
            }



        } catch (SQLException ex) {
        }

        return return_string;

    }

    public String getValue(String table, String Cdata, int id) {
        this.setOpenCon();
        String returnData = this.getFullValue(table, Cdata, " where id='" + id + "'");
        this.setCloseCon();
        return returnData;
    }

    public String getValue(String table, String Cdata, String Where) {
        this.setOpenCon();
        String returnData = this.getFullValue(table, Cdata, Where);
        this.setCloseCon();
        return returnData;
    }
    ////////////////////////////////getValue

    private String Delete(String table, String Where, String id) {
        try {
            st = con.createStatement();

            if (Where.equals("")) {
                sql = "Delete  From " + table + " Where " + Where;
            } else {
                sql = "Delete  From " + table + " Where id=" + id;
            }
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            ErrorMessage += "--" + ex.getMessage();
        }

        return ErrorMessage;
    }

    public String setDelete(String table, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        String returndata = this.Delete(table, "", pid);

        this.setCloseCon();
        return returndata;

    }

    public String setDelete(String table, String Where) {
        this.setOpenCon();
        String returndata = this.Delete(table, Where, "");
        this.setCloseCon();
        return returndata;

    }////////////////////////////////////////////////////

    ////////////////////////////////setUpdate
    private int setFullUpdate(String table, String[][] data, String Where, String id) {

        String valus = "";

        int returnStatus = 0;
        for (int i = 0; i < data[0].length; i++) {
            String spli = ",";
            if (i == 0) {
                spli = "";
            }
            if (data[1][i].indexOf("'") == -1) {
                valus += spli + "`" + data[0][i] + "` = " + "'" + data[1][i] + "'";
            } else {
                valus += spli + "`" + data[0][i] + "` = " + data[1][i];
            }
        }

        try {
            st = con.createStatement();

            if (!Where.equals("")) {

                sql = "Update  `" + table + "`  set " + valus + " Where " + Where;
            } else {

                sql = "Update  `" + table + "`  set " + valus + " Where id=" + id;

            }

            sqlString += "--" + sql;
            returnStatus = st.executeUpdate(sql);

        } catch (SQLException ex) {


            ErrorMessage += "--" + ex.getMessage();
        }


        return returnStatus;
    }

    public int setUpdate(String table, String[][] data, int id) {

        this.setOpenCon();
        String pid = Integer.toString(id);
        int returndata = this.setFullUpdate(table, data, "", pid);
        this.setCloseCon();
        return returndata;
    }

    public int setUpdate(String table, String[][] data, String Where) {

        this.setOpenCon();
        int returndata = this.setFullUpdate(table, data, Where, "");

        this.setCloseCon();
        return returndata;

    }

    ////////////////////////////////setUpdate
    ////////////////////////////////setUpdateValue
    private int setFullUpdateValue(String table, String fieldName, String Value, String Where, String id) {


        int returnStatus = 0;

        try {
            st = con.createStatement();
            if (!Where.equals("")) {
                sql = "Update  `" + table + "`  set " + fieldName + "='" + Value + "' Where " + Where;
            } else {
                sql = "Update  `" + table + "`  set " + fieldName + "='" + Value + "' Where id=" + id;
            }

            sqlString += "--" + sql;
            returnStatus = st.executeUpdate(sql);

        } catch (SQLException ex) {
            ErrorMessage += "--" + ex.getMessage();
        }

        return returnStatus;
    }

    public int setUpdateValue(String table, String fieldName, String Value, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        int returndata = this.setFullUpdateValue(table, fieldName, Value, "", pid);
        this.setCloseCon();
        return returndata;
    }

    public int setUpdateValue(String table, String fieldName, String Value, String Where) {
        this.setOpenCon();
        int returndata = this.setFullUpdateValue(table, fieldName, Value, Where, "");
        this.setCloseCon();
        return returndata;

    }

    ////////////////////////////////setUpdateValue
    ////////////////////////////////setUpdateValue
    private int Insert(String table, String[][] data) {

        String DBflds = "";
        String valus = "";

        for (int i = 0; i < data[0].length; i++) {
            String spli = ",";
            if (i == 0) {
                spli = "";
            }
            DBflds += spli + "`" + data[0][i] + "`";
            valus += spli + "'" + data[1][i] + "'";

        }
        int returnStatus = 0;


        try {
            st = con.createStatement();

            sql = "INSERT INTO `" + table + "` (" + DBflds + ") VALUES (" + valus + ")";

            sqlString += "--" + sql;
            returnStatus = st.executeUpdate(sql);

        } catch (SQLException ex) {
            ErrorMessage = ex.getMessage();

        }

        return returnStatus;

    }

    public int setInsert(String table, String[][] data) {
        int status;
        this.setOpenCon();
        status = Insert(table, data);
        this.setCloseCon();
        return status;
    }

////////////////////////////////setUpdateValue
    ////////////////////////////////getRow
    public String getErrorMessage() {

        return ErrorMessage;
    }

    public String getSqlString() {

        return sqlString;
    }
}
////////////////////////////////////////////////////////////////////////////////////

